package cn.edu.wzu.openlearning.mapper;


import cn.edu.wzu.openlearning.pojo.*;
import org.apache.ibatis.annotations.*;

import java.util.Collection;
import java.util.List;

@Mapper
public interface WordMapper {

    /**
     * 查看词典的单词
     * @return
     */
    @Results(id = "wordMap", value = {
            @Result(id = true, column = "id", property = "wordId"),
            @Result(column = "word", property = "word"),
            @Result(column = "english_pronunciation", property = "englishPronunciation"),
            @Result(column = "america_pronunciation", property = "americaPronunciation"),
            @Result(column = "meaning", property = "meaning"),
    })
    @Select("SELECT * FROM worddict")
    List<Word> selectWordDict();

    /**
     * 统计词典单词的数量
     * @return
     */
    @Select("SELECT count(*) FROM worddict")
    Integer countWordNum();

    /**
     * 搜索单词功能
     * @param word
     * @return
     */
    @ResultMap("wordMap")
    @Select("SELECT * FROM worddict where lower(word) LIKE #{word}")
    Word selectWord(String word);

    /**
     * 通过单词列表找到词库中有的单词，可以去重
     * @param wordList
     * @return
     */
    @ResultMap("wordMap")
    @Select("<script>" +
            "SELECT * FROM open_learning.worddict" +
            "<where>" +
            "<foreach collection='wordList' item='word' open='word IN (' close=')' separator=','>" +
            "#{word}" +
            "</foreach>" +
            "</where>" +
            "</script>")
    List<Word> selectMatchingWords(@Param("wordList") List<String> wordList);

    /**
     * 创建用户自建表
     * @param wordBookNameAndUuid
     * @return
     */
    @Insert("  CREATE TABLE `${value}` ( " +
            "  `id` INT NOT NULL AUTO_INCREMENT," +
            "  `word_id` INT NOT NULL," +
            "  PRIMARY KEY (`id`)," +
            "  UNIQUE INDEX `id_UNIQUE` (`id` ASC))")
    Integer createWordBookByUUid(String wordBookNameAndUuid);

    /**
     * 删除多余的 用户自建表
     * @param wordBookNameAndUuid
     * @return
     */
    @Delete("DROP TABLE #{tableName}")
    Integer deleteTableByWordBookNameAndUUid(@Param("tableName") String wordBookNameAndUuid);

    /**
     * 插入 用户自建表的信息
     * @param userWordBooks
     * @return
     */
    @Insert("INSERT INTO `user_word_books` " +
            "(`user_id`, `word_book_nickname`, `book_uuid`) " +
            "VALUES (#{userId}, #{wordBookNickname}, #{bookUuid})")
    Integer createUserWordBook(UserWordBooks userWordBooks);

    /**
     * 删除用户自建表
     * @param userWordBooks
     * @return
     */
    @Delete("DELETE FROM `user_word_books` WHERE id in (" +
            " select t1.id from " +
            " (select t.id FROM `user_word_books` t " +
            " WHERE t.user_id = #{userId} and t.word_book_nickname = #{wordBookNickname} " +
            "    ) as t1) ")
    Integer deleteUserWordBook(UserWordBooks userWordBooks);

    /**
     * 通过用户的ID和书的昵称查询  要找的表名称
     *
     * @param userId
     * @param nickname
     * @return List<String> 可能有很多个表
     */
    @Select("SELECT t1.book_uuid FROM user_word_books t1 " +
            "where t1.user_id=#{userId} and t1.word_book_nickname=#{nickname}")
    List<String> selectBookByIdAndNickname(@Param("userId") Integer userId, @Param("nickname") String nickname);

    /**
     * 将单词插入到刚刚上传的单词书中
     * @param wordBook
     * @param words
     * @return
     */
    @Insert("<script>" +
            "INSERT INTO `${wordBook}` (`word_id`) VALUES " +
            "<foreach collection='words' item='word' separator=','>" +
            "(#{word.wordId})" +
            "</foreach>" +
            "</script>")
    Integer insertWordInWorkBook(@Param("wordBook") String wordBook, @Param("words") List<Word> words);



    /**
     * 查询可用的单词书
     * @return
     */
    @Results({
            @Result(id = true, column = "id", property = "userWordBooks.id"),
            @Result(column = "user_id", property = "userWordBooks.userId"),
            @Result(column = "word_book_nickname", property = "userWordBooks.wordBookNickname"),
            @Result(column = "book_uuid", property = "userWordBooks.bookUuid"),
    })
    @Select("SELECT * FROM open_learning.user_word_books")
    List<QueryWordBooks> listUserWordBooks();

    /**
     * 计算单词书有多少单词
     * @param tableName
     * @return
     */
    @Select("SELECT count(*) FROM ${value}")
    Integer countWordNumber(String tableName);




    /**
     * 根据用户id 查询该用户的单词背诵设置
     * @param userId
     * @return
     */
    @Results(id = "wordReciteSetting", value = {
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "user_id", property = "userId"),
            @Result(column = "seed", property = "seed"),
            @Result(column = "recite_num", property = "reciteNum"),
            @Result(column = "use_word_book_uuid", property = "useWordBookUuid"),
    })
    @Select("SELECT * FROM word_recite_setting where user_id = #{userId}")
    WordReciteSetting selectWordReciteSettingByUserId(@Param("userId") Integer userId);

    /**
     * 设置用户环境，如果用户没有设置，那么默认设置一下，同时，默认值为seed=123 每天背诵50个
     * @param userId
     */
    @Insert("INSERT INTO `word_recite_setting` (`user_id`, `seed`, `recite_num`) " +
            "VALUES ( #{userId}, 123, 50 )")
    Integer insertWordReciteSettingByUserId(@Param("userId") Integer userId);

    /**
     * 根据用户ID 修改 单词的设置
     * @param seed
     * @param recite_num
     * @param bookId
     * @param userId
     * @return
     */
    @Update("UPDATE `open_learning`.`word_recite_setting` " +
            "SET `seed` = #{seed}, `recite_num` = #{recite_num}, `use_word_book_uuid` = #{bookId} " +
            "WHERE (`user_id` = #{userId})")
    Integer alterWordReciteSettingByUserId(
            @Param("seed") String seed,
            @Param("recite_num") Integer recite_num,
            @Param("bookId") String bookId,
            @Param("userId") Integer userId);

    /**
     * 根据用户id号查询用户熟悉度为0的单词
     * @param userId
     * @return
     */
    @ResultMap("wordMap")
    @Select("select t2.* from ( " +
            " SELECT * FROM user_word_familiarity where user_id = #{userId} and word_familiarity = 0" +
            " ) t1 left join worddict t2 on t1.word_id = t2.id ")
    List<Word> selectWordByFamiliarityEqZero(@Param("userId") Integer userId);


    /**
     * 找用户没有背诵过的单词，打乱，并取出一部分
     * @param tableName
     * @param userId
     * @param seed
     * @param needNumber
     * @return
     */
    @ResultMap("wordMap")
    @Select("select t4.* from  " +
            " (select t1.word_id from ${tableName} t1  " +
            "  where t1.word_id not in  ( " +
            "   SELECT t2.word_id FROM user_word_familiarity t2 where user_id = #{userId}" +
            "  ) order by rand(#{seed}) limit 0,#{needNumber}) t3  " +
            " left join worddict t4 on t3.word_id = t4.id ")
    List<Word> selectRawWord(
            @Param("tableName") String tableName,
            @Param("userId") Integer userId,
            @Param("seed") String seed,
            @Param("needNumber") Integer needNumber
                             );

    /**
     * 插入生单词，熟悉度设置为0
     * @param userId
     * @param words
     * @return
     */
    @Insert("<script>" +
            "  INSERT INTO `open_learning`.`user_word_familiarity` (`user_id`, `word_id`, `word_familiarity`) " +
            "    VALUES " +
            "      <foreach collection='words' item='wordObj' separator=','> " +
            "         (#{userId}, #{wordObj.wordId}, '0') " +
            "      </foreach> " +
            "</script>")
    Integer insertRawWordToWordFamiliarity(@Param("userId") Integer userId, @Param("words") List<Word> words);


    @ResultMap("wordMap")
    @Select("select * from  " +
            "( SELECT t2.* FROM ${tableName} t1 " +
            " left join worddict t2 on t1.word_id = t2.id) t3 where t3.id in  " +
            " (SELECT t4.word_id  " +
            " FROM user_word_familiarity t4  " +
            "   where user_id= #{userId} and t4.word_familiarity = #{wordFamiliarity} and  " +
            "            TIMESTAMPDIFF(Day, date_format(t4.last_update_time,'%Y-%m-%d'), date_format(current_timestamp(),'%Y-%m-%d')) >= #{timeDifference}) " +
            " order by rand(#{seed}) limit 0,#{needNumber}")
    List<Word> selectFamiliarWords(
            @Param("tableName") String tableName,
            @Param("userId") Integer userId,
            @Param("seed") String seed,
            @Param("wordFamiliarity") Integer wordFamiliarity,
            @Param("timeDifference") Integer  timeDifference,
            @Param("needNumber") Integer needNumber
        );


    @Update("<script>" +
            "UPDATE `open_learning`.`user_word_familiarity` SET `word_familiarity` = #{wordFamiliarity} , `last_update_time` = current_timestamp() " +
            " WHERE user_id = #{userId} and word_id in " +
            "      <foreach collection='words' item='wordObj' separator=',' open='(' close=')'  > " +
            "         #{wordObj.wordId}" +
            "      </foreach> " +
            "</script>")
    Integer updateWordsFamiliarity(@Param("words") Collection<Word> words,
                                   @Param("userId") Integer userId,
                                   @Param("wordFamiliarity") Integer wordFamiliarity);

    @Update("<script>" +
            "UPDATE `open_learning`.`user_word_familiarity` SET `word_familiarity` = if(`word_familiarity`!=7,`word_familiarity`+'1',7) , `last_update_time` = current_timestamp() " +
            " WHERE user_id = #{userId} and word_id in " +
            "      <foreach collection='words' item='wordObj' separator=',' open='(' close=')'  > " +
            "         #{wordObj.wordId}" +
            "      </foreach> " +
            "</script>")
    Integer updateAddOneWordsFamiliarity(@Param("words") Collection<Word> words,
                                   @Param("userId") Integer userId);


    @Update("INSERT INTO `open_learning`.`user_recite_history` " +
            "(`user_id`, `user_finished_time`, `recite_words_count`, `review_words_count`, `forget_words_count`) " +
            "VALUES (#{userId},current_timestamp(), #{reciteWordsCount}, #{reviewWordsCount}, #{forgetWordsCount})")
    Integer insertUserWordClockInHistroy(
            @Param("userId") Integer userId,
            @Param("reciteWordsCount") Integer reciteWordsCount,
            @Param("reviewWordsCount") Integer reviewWordsCount,
            @Param("forgetWordsCount") Integer forgetWordsCount
    );


    @Results(value = {
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "user_id", property = "userId"),
            @Result(column = "user_finished_time", property = "userFinishedTime"),
            @Result(column = "recite_words_count", property = "reciteWordsCount"),
            @Result(column = "review_words_count", property = "reviewWordsCount"),
            @Result(column = "forget_words_count", property = "forgetWordsCount"),
    })
    @Select("SELECT * FROM open_learning.user_recite_history where user_id = #{userId}")
    List<UserReciteHistory> listWordHistoryByUserId(@Param("userId") Integer userId);

    @Select("SELECT count(*) FROM ${tableName} t1 " +
            " where t1.word_id in " +
            "   (SELECT word_id FROM user_word_familiarity " +
            "  where user_id = #{userId} and word_familiarity >= 1)")
    Integer countUserRecitedWordNumber(
            @Param("tableName") String tableName,
            @Param("userId") Integer userId
    );

    @Delete("DELETE FROM `open_learning`.`user_word_familiarity` " +
            " WHERE (`user_id` = #{userId})")
    Integer clearAllUserRecitedWord(@Param("userId") Integer userId);

    @Delete("DELETE FROM `open_learning`.`user_word_familiarity`  " +
            " WHERE (`user_id` = 10004) and `word_id` in  " +
            "  (SELECT t1.word_id FROM ${tableName} t1)")
    Integer clearUserRecitedWord(@Param("tableName") String tableName, @Param("userId") Integer userId);


}
